package com.alog.yzd.jdbc;

import com.alog.yzd.utils.Print;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class JdbcTest {
    public static void main(String[] args) throws SQLException {
        JdbcUtils utils = new JdbcUtils();
        Connection conn = utils.getConn();
        Print.print(conn);

        //创建表
//        PreparedStatement ps = conn.prepareStatement("create table user(id integer(20) primary key, name varchar(20), pwd varchar(20));");
//        ps.execute();

        //插入数据
//        testInsert(utils, 1, "Jack", "admin");
//        testInsert(utils, 2, "Rose", "1234");
//        testInsert(utils, 3, "John", "admin333");
//        testInsert(utils, 4, "Mary", "admin555");
//        utils.releaseConn();

        //查询单条记录
//        testFindSimple(utils, 1);
//        testFindSimple(utils, 2);
//        testFindSimple(utils, 3);
//        testFindSimple(utils, 4);
//        utils.releaseConn();

        //查询多条记录
//        testFindMore(utils, 0);
//        testFindMore(utils, 1);
//        testFindMore(utils, 2);
//        testFindMore(utils, 3);
//        utils.releaseConn();

        //查询单个JavaBean
//        testFindRefSimple(utils,1);
//        testFindRefSimple(utils,2);
//        testFindRefSimple(utils,3);
//        testFindRefSimple(utils,4);
//        utils.releaseConn();

        //查询多个JavaBean
        testFindRefMore(utils,0);
        testFindRefMore(utils,1);
        testFindRefMore(utils,2);
        testFindRefMore(utils,3);
        utils.releaseConn();

    }

    /**
     * 查询多个JavaBean
     * @param utils
     * @param id
     */
    private static void testFindRefMore(JdbcUtils utils, int id){
        String sql = "select * from user where id > ?;";
        List<Object> params = new ArrayList<>();
        params.add(id);
        try {
            List<User> result = utils.findMoreRefResult(sql, params, User.class);
            Print.print(result);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 查询单个JavaBean
     * @param utils
     * @param id
     */
    private static void testFindRefSimple(JdbcUtils utils, int id){
        String sql = "select * from user where id = ?;";
        List<Object> params = new ArrayList<>();
        params.add(id);
        try {
            User result = utils.findSimpleRefResult(sql, params, User.class);
            Print.print(result);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 查询多条记录
     * @param utils
     * @param id
     */
    private static void testFindMore(JdbcUtils utils, int id){
        String sql = "select * from user where id > ?;";
        List<Object> params = new ArrayList<>();
        params.add(id);
        try {
            List<Map<String, Object>> moreResult = utils.findMoreResult(sql, params);
            Print.print(moreResult);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 查询单条记录
     * @param utils
     * @param id
     */
    private static void testFindSimple(JdbcUtils utils, int id){
        String sql = "select * from user where id = ?;";
        List<Object> params = new ArrayList<>();
        params.add(id);
        try {
            Map<String, Object> map = utils.findSimpleResult(sql, params);
            Print.print(map);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 插入数据
     * @param utils
     * @param id
     * @param name
     * @param pwd
     */
    private static void testInsert(JdbcUtils utils, Integer id, String name, String pwd){
        String sql = "insert into user(id, name, pwd) values (?,?,?);";
        List<Object> params = new ArrayList<>();
        params.add(id);
        params.add(name);
        params.add(pwd);
        try {
            boolean result = utils.updateByPreparedStatement(sql, params);
            if(result){
                Print.print("成功添加一条数据, name:" + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
